package cn.com.longfuinfo.wx.service;

import java.util.ArrayList;
import java.util.List;

import com.jfinal.kit.StrKit;
import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Page;
import com.jfinal.plugin.activerecord.Record;

import cn.com.longfuinfo.wx.common.OrderCode;
import cn.com.longfuinfo.wx.dao.BabyTitle;
import cn.com.longfuinfo.wx.dao.GoodSpec;
import cn.com.longfuinfo.wx.dao.Goods;
import cn.com.longfuinfo.wx.dao.Order;
import cn.com.longfuinfo.wx.dao.Store;
import cn.com.longfuinfo.wx.dao.UserLook;

public class GoodsService {

	/**
	 * 查询商品列表
	 * 
	 * @param storeid
	 *            店铺ID
	 * @param categoryonCode
	 *            一级分类代码
	 * @param categorytwCode
	 *            二级分类代码
	 * @param isSelf
	 *            是否筛选自营商品，1：是，其他：否
	 * @param pageNum
	 *            页码
	 * @param pageSize
	 *            数量
	 * @param orderBy
	 *            排序，0：默认排序，1：销量从高到低，2：价格从低到高，3：价格从高到低
	 */
//	public Page<Record> findByPage(int pageNum, int pageSize) {
//		String sqlSelect = "SELECT g.id,g.name,g.thumb,g.spec,i.id itemId,i.title itemTitle,i.sellPrice itemPrice ";
//		StringBuffer sqlFromSelect = new StringBuffer("FROM t_bus_goods g "
//				+ "LEFT JOIN (SELECT * FROM (SELECT id,goodsid,title,sellPrice FROM t_bus_goods_items ORDER BY goodsid,sellPrice ASC) gi GROUP BY goodsid) i ON g.id = i.goodsid ");
//		StringBuffer sqlWhereSelect = new StringBuffer("WHERE g.state=1 order by g.id desc");
//		return Db.paginate(pageNum, pageSize, sqlSelect, sqlFromSelect.append(sqlWhereSelect).toString());
//	}

	/**
	 * 查询商品指定信息
	 * 
	 * @param id
	 *            商品id
	 */
	public Goods getGoodById(int id) {
		return Goods.me().findById(id);
	}	
/***
 * 根据支付类型积分类型或商品类型
 * 查询商品列表
 * @param cityId 城市id
 * @param kind 1：销量优先排序 2：价格/积分升序 3：价格/积分降序
 * @param paytype 1:全部  2：商品积分/孕期产品  3：健康积分/产后健康 4：教育积分/育婴教育
 * @param type 1：货币支付  2：积分支付  3：积分+货币支付
 * @param minPrice 最小价格
 * @param maxPrice 最大价格
 * @param minIntegral 最小积分
 * @param maxIntegral 最大积分
 * @param pageNum
 * @param pageSize
 * @return
 */
public Page<Goods> findByJFPage(int cityId, int kind, int paytype, int type, String minPrice, String maxPrice,
		String minIntegral, String maxIntegral, int pageNum, int pageSize) {
	List<Object> params = new ArrayList<Object>();	
	params.add(cityId);
	String sql = "FROM t_bus_goods bo LEFT JOIN t_bus_goods_store_rel AS tbgs ON tbgs.goodstore= bo.good_store "
			+ "LEFT JOIN t_dict_store AS tds ON tds.storeid= tbgs.storeid  LEFT JOIN t_bus_goods_spec spec on spec.goodId=bo.id "
			+ "  LEFT JOIN (SELECT sum(bb.countnum) saleCount,bb.goodsid FROM t_bus_order bb  WHERE  bb.payState=2 group by bb.goodsid ) B on B.goodsid=bo.id  WHERE 1=1  "
			+ " and bo.pstate=1 and bo.state=2 and tds.qstate!=3 and tds.state=2 and tds.city=? ";
	
	//支付类型查询条件
	if(type==1){
		//货币支付
		sql += " AND spec.currency!=0 ";
	}else if(type==2){
		//积分支付
		sql += " AND spec.integral!=0 ";
	}else if(type==3){
		//积分支付+货币支付
		sql += " AND spec.integCur_i!=0 ";
	}
	
	//积分类型或商品类型
	switch (paytype) {
		case 1:
			// 1:全部惠商城孕期商品   产前:713, 产后714 ， 育英教育  ，  儿童乐园:720，儿童摄影:721, 幼儿教育:722, 玩具出行:724,婴儿游泳:726,其他类:727,728
			
			break;
		case 2:
			//2:商品积分   惠商城孕期商品   categoryonid=713  产前分类id
			if(type==1){				
				sql += " AND bo.categoryonid=713 ";
			}else if(type==2){
				sql += "  and spec.integralType=1";
			}else{
				//积分 +货币
				sql += "  and spec.integCur_iType=1";
			}		
			break;
		case 3:
			// 3：健康积分惠商城 产后健康  产后：714   母婴护理 ：723  月子会所：725
			if(type==1){				
				sql += "  AND  bo.categoryonid=714 and bo.categorytwid IN (723,725) ";
			}else if(type==2){
				sql += "  and spec.integralType=2";
			}else{
				//积分 +货币
				sql += "  and spec.integCur_iType=2";
			}				
			break;
		default:
			//4:教育积分  惠商城 育英教育 产后:714    儿童乐园:720，儿童摄影:721, 幼儿教育:722, 玩具出行:724,婴儿游泳:726,其他类:727,728
			if(type==1){			
				sql += " AND bo.categoryonid=714 and  bo.categorytwid IN (720,721,722,724,726,727,728) ";
			}else if(type==2){
				sql += "  and spec.integralType=3";
			}else{
				//积分 +货币
				sql += "  and spec.integCur_iType=3";
			}		
			break;
	}
	
	//积分区间查询或价格区间查询
	if (!StrKit.isBlank(minPrice) || !StrKit.isBlank(maxPrice) || !StrKit.isBlank(minIntegral)
			|| !StrKit.isBlank(maxIntegral)) {	
		return findBySortPage(sql,cityId, kind, paytype, type, minPrice, maxPrice,minIntegral,maxIntegral, pageNum, pageSize);
	}
	
	
		return findByPage(sql,kind,type,params,pageNum,pageSize);
	
}

/**
 * 按积分区间/价格区间查询
 * 
 * @param cityId 城市id
 * @param kind 1：销量优先排序 2：价格/积分升序 3：价格/积分降序
 * @param paytype 1:全部  2：商品积分/孕期产品  3：健康积分/产后健康 4：教育积分/育婴教育
 * @param type 1：货币支付  2：积分支付  3：积分+货币支付
 * @param minPrice 最小价格
 * @param maxPrice 最大价格
 * @param minIntegral 最小积分
 * @param maxIntegral 最大积分
 * @return
 */
public Page<Goods> findBySortPage(String sql,int cityId, int kind, int paytype, int type, String minPrice, String maxPrice,
		String	minIntegral,String maxIntegral,int pageNum, int pageSize) {		
	List<Object> params = new ArrayList<Object>();	
	params.add(cityId);
	//积分与价格同时查询
	if ((!StrKit.isBlank(minPrice) || !StrKit.isBlank(maxPrice)) || (!StrKit.isBlank(minIntegral)
			|| !StrKit.isBlank(maxIntegral))){
	     // 二者之间
		if (!StrKit.isBlank(minIntegral) && !StrKit.isBlank(maxIntegral) && !StrKit.isBlank(minPrice) && !StrKit.isBlank(maxPrice)) {
			if(type==3){
				sql += " and spec.integCur_i BETWEEN ? AND ?  and spec.integCur_c BETWEEN ? AND ? ";
			}else{
			sql += " and spec.integral BETWEEN ? AND ? and spec.currency BETWEEN ? AND ?  ";					
			}	
			params.add(minIntegral);
			params.add(minPrice);
			params.add(maxIntegral);
			params.add(maxPrice);
		} else if (!StrKit.isBlank(minIntegral)  && !StrKit.isBlank(minPrice)) {
           //最小
			if(type==3){
				sql += " and spec.integCur_i>=? and spec.integCur_c>=?";
			}else{
				sql += " and spec.integral>=? and spec.currency>=? ";				
			}	
			params.add(minIntegral);
			params.add(minPrice);
		} else if (!StrKit.isBlank(maxIntegral) && !StrKit.isBlank(maxPrice)) {
			//最大
			if(type==3){
				sql += " and spec.integCur_i<=? and spec.integCur_c<=?";
			}else{				
			  sql += " and spec.integral<=? and spec.currency<=?";			    
			}
			params.add(maxIntegral);
			params.add(maxPrice);
		}else if (!StrKit.isBlank(maxIntegral) && !StrKit.isBlank(minPrice)) {
			//最大积分与最小价格
			if(type==3){
				sql += " and spec.integCur_i<=? and spec.integCur_c<=?";
			}else{				
			  sql += " and spec.integral<=? and spec.currency<=?";			   
			}
			 params.add(maxIntegral);
				params.add(minPrice);
		}else if (!StrKit.isBlank(minIntegral) && !StrKit.isBlank(maxPrice)) {
			//最小积分与最大价格
			if(type==3){
				sql += " and spec.integCur_i<=? and spec.integCur_c<=?";
			}else{				
			  sql += " and spec.integral<=? and spec.currency<=?";			   
			}
			 params.add(minIntegral);
			 params.add(maxPrice);
	}else if (!StrKit.isBlank(minPrice) || !StrKit.isBlank(maxPrice) || !StrKit.isBlank(minIntegral)
			|| !StrKit.isBlank(maxIntegral)) {		
		//积分或价格查询
		//积分区间查询 二者之间
		if (!StrKit.isBlank(minIntegral) && !StrKit.isBlank(maxIntegral)) {
			if(type==3){
				sql += " and spec.integCur_i BETWEEN ? AND ? ";
			}else{
			sql += " and spec.integral BETWEEN ? AND ?  ";				
			}
			params.add(minIntegral);
			params.add(maxIntegral);
		} else if (!StrKit.isBlank(minIntegral)) {
            //最小
			if(type==3){
				sql += " and spec.integCur_i>=? ";
			}else{
				sql += " and spec.integral>=? ";				
			}
			params.add(minIntegral);
			
		} else if (!StrKit.isBlank(maxIntegral)) {
			//最大
			if(type==3){
				sql += " and spec.integCur_i<=? ";
			}else{				
			  sql += " and spec.integral<=? ";				
			}
			params.add(maxIntegral);
		}

		//价格区间查询
		//二者之间
		if (!StrKit.isBlank(minPrice) && !StrKit.isBlank(maxPrice)) {
			if(type==3){
			    sql += " and spec.integCur_c BETWEEN ? AND ?  ";
			}else{
				sql += " and spec.currency BETWEEN ? AND ? ";				
			}
			 params.add(minPrice);
			 params.add(maxPrice);
		}
		  else if (!StrKit.isBlank(minPrice)) {
			//最小
			if(type==3){
			    sql += " and spec.integCur_c>=? ";
			}else{
			    sql += " and spec.currency>=? ";				    
			}
			params.add(minPrice);
		} else if (!StrKit.isBlank(maxPrice)) {
			//最大
			if(type==3){
				sql += " and spec.integCur_c<=?";
			}else{
			    sql += " and spec.currency<=? ";				
			}
			params.add(maxPrice);
		}
	 }
}
		return findByPage(sql,kind,type,params,pageNum,pageSize);
}

/**
 * 查询商品数据  排序
 * @param sql
 * @param kind
 * @param type
 * @param params
 * @param pageNum
 * @param pageSize
 * @return
 */
public Page<Goods> findByPage(String sql,int kind,int type,List params,int pageNum, int pageSize){
	// 1:销量优先排序
	if (kind == 1) {
			sql += " group by bo.id ORDER BY B.saleCount DESC,bo.id desc";
		} else if (kind == 2) {
		// 兑换积分升序

		if(type==1){
			sql += " group by bo.id ORDER BY spec.currency asc,bo.id asc";
		}else if(type==2){
			sql += " group by bo.id ORDER BY spec.integral asc,bo.id asc";
		}else{
		//支付类型为3		
			sql += " group by bo.id ORDER BY spec.integCur_i asc,bo.id asc";
		}

	} else if (kind == 3) {
		// 兑换积分降序
		
		//支付类型不为3
		if(type==1){
			//货币
			sql += " group by bo.id ORDER BY spec.currency DESC,bo.id desc";
		}else if(type==2){
			//积分
			sql += " group by bo.id ORDER BY spec.integral DESC,bo.id desc";
		}else{
		   //积分+货币
			sql += " group by bo.id ORDER BY spec.integCur_i DESC,bo.id desc";
		}
	}
	
	return Goods.me().paginate(pageNum, pageSize,
			"SELECT bo.id,bo.thumb,bo.name,IFNULL(B.saleCount,0) saleCount,spec.integral,spec.currency,cast(spec.marketPrice as char(11)) marketSpecPrice,spec.integCur_i,spec.integCur_c,spec.integralType,spec.integCur_iType  ",
			sql,params.toArray());
}

	public Goods findById(int id) {
		String sql = "select A.* ,GOC.stockNum,IFNULL(B.saleCount,0) saleCount,"
				+ "(SELECT COUNT(*) FROM t_bus_user_collection buc WHERE buc.goodsid=? AND buc.isCancel=0) collectedCount from t_bus_goods A "
				+ "LEFT JOIN (SELECT sum(countnum) saleCount, goodsid FROM t_bus_order  WHERE payState=? group by goodsid) B on B.goodsid=A.id "
				+ " left join t_bus_goods_spec GOC on GOC.goodId=A.id where A.id=?";
		return Goods.me().findFirst(sql, id, OrderCode.OrderPayState.HAD_PAY, id);
	}
	
	public GoodSpec findGoodSpecByGoodsId(int goodId) {
		return GoodSpec.me().findFirst("select * from t_bus_goods_spec where goodId=?", goodId);
	}

/*	public Goods findById(int userid, int id) {
		String sql = "select A.*,IFNULL(B.saleCount,0) saleCount,IFNULL(C.xCount,0) xCount,"
				+ "(SELECT COUNT(*) FROM t_bus_user_collection buc WHERE buc.userid=? AND buc.goodsid=A.id AND buc.isCancel=0)!=0 isCollected,"
				+ "(SELECT COUNT(*) FROM t_bus_user_collection buc WHERE buc.goodsid=? AND buc.isCancel=0) collectedCount  from t_bus_goods A "
				+ "LEFT JOIN (SELECT sum(bo.countnum) saleCount,bo.goodsid FROM t_bus_order bo  WHERE bo.payState=? group by bo.goodsid ) B on B.goodsid=A.id "
				+ "LEFT JOIN (SELECT sum(boo.countnum) xCount,boo.goodsid,boo.orderState FROM t_bus_order boo  WHERE  boo.payState=? and boo.userid=? and boo.tuistate=0 group by boo.goodsid,boo.orderState having boo.orderState !=3) C on C.goodsid=A.id "
				+ " where A.id=?";
		return Goods.me().findFirst(sql, userid, id, OrderCode.OrderPayState.HAD_PAY, OrderCode.OrderPayState.HAD_PAY,
				userid, id);
	}*/
	
	public Goods findById(int userid, int id) {
		String sql = "select A.*,IFNULL(B.saleCount,0) saleCount,IFNULL(C.xCount,0) xCount,"
				+ "(SELECT COUNT(*) FROM t_bus_user_collection buc WHERE buc.userid=? AND buc.goodsid=A.id AND buc.isCancel=0)!=0 isCollected,"
				+ "(SELECT COUNT(*) FROM t_bus_user_collection buc WHERE buc.goodsid=? AND buc.isCancel=0) collectedCount  from t_bus_goods A "
				+ "LEFT JOIN (SELECT sum(bb.countnum) saleCount,bb.goodsid FROM t_bus_order bb  WHERE  bb.payState=2 group by bb.goodsid ) B on B.goodsid=A.id "
				+ "LEFT JOIN (SELECT sum(boo.countnum) xCount,boo.goodsid,boo.orderState FROM t_bus_order boo  WHERE   boo.payState=2 and boo.userid=? and boo.tuistate=0 group by boo.goodsid,boo.orderState having boo.orderState !=3) C on C.goodsid=A.id "
				+ " where A.id=?";
		return Goods.me().findFirst(sql, userid, id, 
				userid, id);
	}
	
	public List<Goods> findGoodByKeyWord(String keyword, int cityId) {
		String sql = "select tbg.* from t_bus_goods AS tbg INNER JOIN t_bus_goods_store_rel AS tbgs ON tbgs.goodstore= tbg.good_store "
				+ "INNER JOIN t_dict_store AS tds ON tds.storeid= tbgs.storeid where tbg.pstate !=3 and tbg.state=2 and tds.qstate!=3 and tds.state=2 and tds.city=? and  tbg.name like ?";
		return Goods.me().find(sql, cityId, "%" + keyword + "%");
	}

	public Page<Record> pageByjf(int cityId, int pageNum, int pageSize) {
		String sql = "select DISTINCT tbg.id,tbg.paytype,cast(tbg.marketPrice as char(11)) marketPrice,tbg.sellpoint,tbg.thumb ";
		String sqlwhere = "FROM t_bus_goods AS tbg INNER JOIN t_bus_goods_store_rel AS tbgs ON tbgs.goodstore= tbg.good_store INNER JOIN t_dict_store AS tds "
				+ "ON tds.storeid= tbgs.storeid where tbg.type=2 and tbg.pstate !=3 and tbg.state=2 and tds.qstate!=3 and tds.state=2 and tds.city=? GROUP BY tbg.id order by tbg.createDate desc";
		return Db.paginate(pageNum, pageSize, sql, sqlwhere, cityId);
	}

//	public Page<Record> pageByxy(int cityId, int pageNum, int pageSize) {
//	
//		String sql = "SELECT DISTINCT tbg.id,tbg.`name`,tbg.type,cast(spec.currency as char(11)) sellPrice,spec.*,cast(spec.marketPrice as char(11)) marketPrice,tbg.thumb,IFNULL(tbo.counts,0)as countnum";
//		String sqlwhere = "FROM t_bus_goods AS tbg INNER JOIN t_bus_goods_store_rel AS tbgs ON tbgs.goodstore = tbg.good_store INNER JOIN t_dict_store AS tds ON "
//				+ "tds.storeid = tbgs.storeid LEFT JOIN (select goodsid,SUM(countnum) as counts from t_bus_order where payState=2 GROUP BY goodsid) tbo ON tbo.goodsid=tbg.id LEFT JOIN t_bus_goods_spec spec on spec.goodId=tbg.id"
//				+ "WHERE tbg.pstate !=3 and tbg.state=2 and tds.qstate!=3 and tds.state=2 and tds.city = ? GROUP BY tbg.id ORDER BY countnum desc ";
//		return Db.paginate(pageNum, pageSize, sql, sqlwhere, cityId);
//	}
//
//	public Page<Record> pageByxys(int cityId, int pageNum, int pageSize, int goods) {
//		String sql = "SELECT DISTINCT tbg.id,tbg.`name`,tbg.type,cast(spec.sellPrice as char(11)) sellPrice,cast(spec.marketPrice as char(11)) marketPrice,tbg.thumb,IFNULL(tbo.counts,0)as countnum";
//		String sqlwhere = "FROM t_bus_goods AS tbg INNER JOIN t_bus_goods_store_rel AS tbgs ON tbgs.goodstore = tbg.good_store INNER JOIN t_dict_store AS tds ON "
//				+ "tds.storeid = tbgs.storeid LEFT JOIN (select goodsid,SUM(countnum) as counts from t_bus_order where payState=2 GROUP BY goodsid ) tbo ON tbo.goodsid=tbg.id LEFT JOIN t_bus_goods_spec spec on spec.goodId=tbg.id"
//				+ "WHERE tbg.pstate !=3 and tbg.state=2 and tds.qstate!=3 and tds.state=2 and tds.city = ? and tbg.categoryonid=(select categoryonid from t_bus_goods where id=?)"
//				+ " GROUP BY tbg.id ORDER BY countnum desc ";
//		return Db.paginate(pageNum, pageSize, sql, sqlwhere, cityId, goods);
//	}

	public Record findLastOrder(int userId) {
		return Db.findFirst("select goodsid from t_bus_order where userid=? order by startDate desc", userId);
	}
	
	/***
	 * 根据商品id查询商品所属商家类型
	 */
	public Record getGoodsSellerById(int goodsId){
		return Db.findFirst("select tbs.id,tbs.sellerId from t_bus_goods tbg" 
				 +" left join t_bus_goods_store_rel AS tbgs ON tbgs.goodstore= tbg.good_store"
				 +" left join t_dict_store AS tds ON tds.storeid=tbgs.storeid"
				 +" INNER JOIN t_bus_seller tbs ON tds.sellerId=tbs.sellerId"
				 +" where tbg.id=? group by tbs.sellerId",goodsId);
	}
	
	public List<Record> findImage(String LunBoNum) {
		return Db.find("select thumb from t_bus_goods_base_images where LunBoNum=? order by orderNo asc ", LunBoNum);
	}

	public List<Record> findDetailImage(String LunBoNum) {
		return Db.find("select thumb from goods_details_images where LunBoNum=? order by orderNo asc ", LunBoNum);
	}

	public List<Record> getGoodsItem(String gooditem) {
		return Db.find("select * from t_bus_goods_item where goodItem=?", gooditem);
	}

	public List<Record> getStore(String good_store) {
		return Db.find(
				"SELECT A.storeid,A.title,A.address,A.tel,A.sellerId FROM t_dict_store A WHERE A.state=2 and A.qstate!=3 and A.storeid IN (select storeid from t_bus_goods_store_rel where goodstore=?) order by A.createtime desc",
				good_store);
	}

	public Page<Record> pageBytui(int pageNum, int pageSize) {
		String sql = "select  tbg.id, tbg.`name`,tbg.type,cast(tbg.sellPrice as char(11)) sellPrice,cast(tbg.marketPrice as char(11)) marketPrice,tbg.paytype,tbg.sellpoint,tbg.thumb,IFNULL(sss.countnum,0) as num,B.link ";
		String sqlwhere = "FROM t_bus_goods AS tbg  inner JOIN t_bus_goods_base_baby B on tbg.id=B.goodsid"
				+ " LEFT JOIN (select goodsid ,SUM(countnum) as countnum  from t_bus_order where payState=2 GROUP BY goodsid) sss on sss.goodsid=tbg.id"
				+ " where 1=1  ORDER BY B.orderNo ";
		return Db.paginate(pageNum, pageSize, sql, sqlwhere);
	}

	public Page<Record> pageByxin(int cityId, int pageNum, int pageSize) {
		String sql = "SELECT DISTINCT tbg.id,tbg.`name`,tbg.type,cast(tbg.sellPrice as char(11)) sellPrice,cast(tbg.marketPrice as char(11)) marketPrice,tbg.paytype,tbg.sellpoint,tbg.thumb,IFNULL(tbo.counts,0)as countnum";
		String sqlwhere = "FROM t_bus_goods AS tbg INNER JOIN t_bus_goods_store_rel AS tbgs ON tbgs.goodstore = tbg.good_store INNER JOIN t_dict_store AS tds ON "
				+ "tds.storeid = tbgs.storeid LEFT JOIN (select goodsid,SUM(countnum) as counts from t_bus_order where payState=2 GROUP BY goodsid) tbo ON tbo.goodsid=tbg.id "
				+ "WHERE tbg.pstate !=3 and tbg.state=2 and tds.qstate!=3 and tds.state=2 and tds.city = ? GROUP BY tbg.id ORDER BY tbg.uptime desc ";
		return Db.paginate(pageNum, pageSize, sql, sqlwhere, cityId);
	}

	public Page<Record> pageBycq(int cityId, int pageNum, int pageSize) {
		String sql = "select DISTINCT tbg.id, tbg.`name`,tbg.type,cast(tbg.sellPrice as char(11)) sellPrice,cast(tbg.marketPrice as char(11)) marketPrice,tbg.paytype,tbg.sellpoint,tbg.thumb,IFNULL(sss.countnum,0) as num ";
		String sqlwhere = "FROM t_bus_goods AS tbg INNER JOIN t_bus_goods_store_rel AS tbgs ON tbgs.goodstore= tbg.good_store INNER JOIN t_dict_store AS tds "
				+ "ON tds.storeid= tbgs.storeid LEFT JOIN (select goodsid ,SUM(countnum) as countnum  from t_bus_order where payState=2 GROUP BY goodsid) sss on sss.goodsid=tbg.id"
				+ " where tbg.categoryonid=713 and tds.qstate!=3 and tds.state=2 and tbg.pstate !=3 and tbg.state=2 and tds.city=? GROUP BY tbg.id ORDER BY num desc ";
		return Db.paginate(pageNum, pageSize, sql, sqlwhere, cityId);
	}

	public Page<Record> pageBych(int cityId, int pageNum, int pageSize) {
		String sql = "select DISTINCT tbg.id, tbg.`name`,tbg.type,cast(tbg.sellPrice as char(11)) sellPrice,cast(tbg.marketPrice as char(11)) marketPrice,tbg.paytype,tbg.sellpoint,tbg.thumb,IFNULL(sss.countnum,0) as num ";
		String sqlwhere = "FROM t_bus_goods AS tbg INNER JOIN t_bus_goods_store_rel AS tbgs ON tbgs.goodstore= tbg.good_store INNER JOIN t_dict_store AS tds "
				+ "ON tds.storeid= tbgs.storeid LEFT JOIN (select goodsid ,SUM(countnum) as countnum  from t_bus_order where payState=2 GROUP BY goodsid) sss on sss.goodsid=tbg.id"
				+ " where tbg.categoryonid=714 and tds.qstate!=3 and tds.state=2 and tbg.pstate !=3 and tbg.state=2 and tds.city=? GROUP BY tbg.id ORDER BY num desc ";
		return Db.paginate(pageNum, pageSize, sql, sqlwhere, cityId);
	}

	public Page<Record> goodList(int cityId, int pageNum, int pageSize, int categoryonid, int categorytwid) {
		String sql = "select DISTINCT tbg.id, tbg.`name`,tbg.type,cast(tbg.sellPrice as char(11)) sellPrice,cast(tbg.marketPrice as char(11)) marketPrice,tbg.paytype,tbg.sellpoint,tbg.thumb,IFNULL(sss.countnum,0) as num ";
		String sqlwhere = "FROM t_bus_goods AS tbg INNER JOIN t_bus_goods_store_rel AS tbgs ON tbgs.goodstore= tbg.good_store INNER JOIN t_dict_store AS tds "
				+ "ON tds.storeid= tbgs.storeid LEFT JOIN (select goodsid ,SUM(countnum) as countnum  from t_bus_order where payState=2 GROUP BY goodsid) sss on sss.goodsid=tbg.id"
				+ " where tbg.categoryonid=? and tbg.categorytwid=? and tds.qstate!=3 and tds.state=2 and tbg.pstate !=3 and tbg.state=2 and tds.city=? GROUP BY tbg.id ORDER BY num desc ";
		return Db.paginate(pageNum, pageSize, sql, sqlwhere, categoryonid, categorytwid, cityId);
	}

	/* 店铺图 */
	public List<Record> findStoreImage(String stroeimagenum) {
		return Db.find("SELECT thumb FROM t_bus_store_images  WHERE stroeimagenum=? ORDER BY orderNo", stroeimagenum);
	}

	public Store findStore(String storeid) {
		return Store.me().findFirst(
				"SELECT td.storeid,td.title,td.stroeimagenum,td.address,td.tel,td.sellerId,IFNULL(COUNT(tbg.id),0) as num  FROM t_dict_store AS td INNER JOIN t_bus_seller AS tb ON td.sellerId=tb.sellerId "
						+ "LEFT JOIN t_bus_goods_store_rel tbgs on td.storeid=tbgs.storeid LEFT JOIN t_bus_goods tbg ON tbg.good_store=tbgs.goodstore "
						+ "where td.qstate!=3 and td.state=2 and tb.state=2 and tbg.pstate !=3 and tbg.state=2 and td.storeid=? GROUP BY td.storeid",
				storeid);
	}

	public Page<Record> pageStore(int pageNum, int pageSize, String storeid) {
		String sql = "select DISTINCT tbg.id,tds.storeid, tbg.`name`,tbg.type,cast(tbg.sellPrice as char(11)) sellPrice,cast(tbg.marketPrice as char(11)) marketPrice,tbg.paytype,tbg.sellpoint,tbg.thumb,IFNULL(sss.countnum,0) as num,gc.integral,"
				+ "gc.currency,gc.currency,gc.integCur_i,gc.integCur_c,gc.integralType,gc.integCur_iType ";
		String sqlwhere = "FROM t_bus_goods AS tbg INNER JOIN t_bus_goods_store_rel AS tbgs ON tbgs.goodstore= tbg.good_store INNER JOIN t_dict_store AS tds "
				+ "ON tds.storeid= tbgs.storeid LEFT JOIN (select goodsid ,SUM(countnum) as countnum  from t_bus_order where payState=2 GROUP BY goodsid) sss on sss.goodsid=tbg.id "
				+ " LEFT JOIN  t_bus_goods_spec gc on gc.goodId=tbg.id "
				+ " where tds.qstate!=3 and tds.state=2 and tbg.pstate !=3 and tbg.state=2 and tds.storeid=? GROUP BY tbg.id ORDER BY tbg.createDate desc ";
		return Db.paginate(pageNum, pageSize, sql, sqlwhere, storeid);
	}

	public List<Record> storeList(String storeid, String sellerId) {
		return Db.find(
				"select td.storeid,td.title,td.address,td.tel,td.sellerId from t_dict_store td inner join t_bus_seller tb on td.sellerId=tb.sellerId "
						+ "where tb.state=2 and td.qstate !=3 and td.state=2 and tb.sellerId=? and td.storeid !=?",
				sellerId, storeid);
	}

	public Record getStoreById(String storeid) {
		return Db.findFirst("SELECT storeid,title,address,tel,sellerId from t_dict_store where storeid=?", storeid);
	}

	public List<Record> goodsOtherStore(String storeid, String sellerId, int goodsid) {
		return Db.find(
				"SELECT td.storeid,td.title,td.address,td.tel,td.sellerId,tbg.id FROM t_dict_store td INNER JOIN t_bus_seller tb ON td.sellerId = tb.sellerId INNER JOIN t_bus_goods_store_rel tbgs on td.storeid=tbgs.storeid "
						+ "INNER JOIN t_bus_goods tbg on tbg.good_store =tbgs.goodstore WHERE tb.state = 2 AND td.qstate != 3 AND td.state = 2 AND tb.sellerId =? AND td.storeid !=? and tbg.id=?",
				sellerId, storeid, goodsid);
	}

	public Page<Record> serchList(int cityId, int pageNum, int pageSize, String keyword) {
		String sql = "select DISTINCT tbg.id, tbg.`name`,tbg.type,cast(tbg.sellPrice as char(11)) sellPrice,cast(tbg.marketPrice as char(11)) marketPrice,tbg.paytype,tbg.sellpoint,tbg.thumb,IFNULL(sss.countnum,0) as num ";
		String sqlwhere = "FROM t_bus_goods AS tbg INNER JOIN t_bus_goods_store_rel AS tbgs ON tbgs.goodstore= tbg.good_store INNER JOIN t_dict_store AS tds "
				+ "ON tds.storeid= tbgs.storeid LEFT JOIN (select goodsid ,SUM(countnum) as countnum  from t_bus_order where payState=2 GROUP BY goodsid) sss on sss.goodsid=tbg.id"
				+ " where  tds.qstate!=3 and tds.state=2 and tbg.pstate !=3 and tbg.state=2 and tds.city=? and  tbg.name like ? GROUP BY tbg.id ORDER BY num desc ";
		return Db.paginate(pageNum, pageSize, sql, sqlwhere, cityId, "%" + keyword + "%");
	}

	public Goods findGoodsById(String goodsid) {
		return Goods.me().findFirst(
				"select tbg.* from t_bus_goods AS tbg INNER JOIN t_bus_goods_store_rel AS tbgs ON tbgs.goodstore= tbg.good_store INNER JOIN t_dict_store AS tds ON tds.storeid= tbgs.storeid where tbg.id=?",
				goodsid);
	}

	public Record getCateName(int categorytwid) {
		return Db.findFirst("select categoryName from t_dict_category where categoryId=?", categorytwid);
	}

	public UserLook findLook(int goodsid) {
		return UserLook.me().findFirst("select * from t_bus_goods_look where goodsid=?", goodsid);
	}

	public BabyTitle findBabyTitle() {
		return BabyTitle.me().findFirst("select * from t_bus_goods_base_baby_title");
	}

	public Record fingShiYong() {
		return Db.findFirst("select * from t_wx_article where categoryid=2  and isTop =1");
	}

	public Record fingzhuanti() {
		return Db.findFirst("select * from t_wx_article where categoryid=1  and isTop =1");
	}

	public Record fingShiYongBaoGao() {
		return Db.findFirst("select * from t_wx_article where categoryid=3  and isTop =1");
	}

	public Page<Record> mamiList(int pageNum, int pageSize) {
		String sql = "select A.title,A.categoryid,A.`describe`,A.thumb,A.`context`,date_format(A.createDate,'%Y-%m-%d') createDate ";
		String sqlwhere = " from t_wx_article A where A.isTop=1 order by A.createDate desc";
		return Db.paginate(pageNum, pageSize, sql, sqlwhere);
	}
	/***
	 * 首页商品展示（畅销热品）
	 * @param cityId
	 * @param pageNum
	 * @param pageSize
	 * @return
	 */
	public Page<Record> pageBesell( int pageNum, int pageSize) {
		/*String code="S00001";*///畅销热品编码
		String sql = "SELECT bo.id,bo.thumb,bo.name,cast(spec.marketPrice as char(11)) marketPrice,IFNULL(B.saleCount,0) saleCount,spec.integral,spec.currency,spec.integCur_i,spec.integCur_c,spec.integralType,spec.integCur_iType  ";
		String sqlwhere=" FROM t_bus_goods bo  LEFT JOIN t_bus_goods_spec spec on spec.goodId=bo.id "
				+ " LEFT JOIN (SELECT sum(countnum) saleCount,goodsid FROM t_bus_order WHERE payState=2 group by goodsid) B on B.goodsid=bo.id "    
				+" where bo.titleCord like '%S00001%' and bo.state=2 group by bo.id  ORDER BY saleCount DESC,bo.id desc";
		return Db.paginate(pageNum, pageSize, sql, sqlwhere);
	}
	/***
	 * 首页商品展示（畅销热品限本地区）
	 * @param cityId
	 * @param pageNum
	 * @param pageSize
	 * @return
	 */
	public Page<Record> pageBesell( int pageNum, int pageSize,int cityId) {
		/*String code="S00001";*///畅销热品编码
		String sql = "SELECT bo.id,bo.thumb,bo.name,cast(spec.marketPrice as char(11)) marketPrice,IFNULL(B.saleCount,0) saleCount,spec.integral,spec.currency,spec.integCur_i,spec.integCur_c,spec.integralType,spec.integCur_iType  ";
		String sqlwhere=" FROM t_bus_goods bo LEFT JOIN t_bus_goods_store_rel AS tbgs ON tbgs.goodstore= bo.good_store"
			+" LEFT JOIN t_dict_store AS tds ON tds.storeid= tbgs.storeid  LEFT JOIN t_bus_goods_spec spec on spec.goodId=bo.id "
				+ " LEFT JOIN (SELECT sum(countnum) saleCount,goodsid FROM t_bus_order WHERE payState=2 group by goodsid) B on B.goodsid=bo.id "    
				+" where bo.titleCord like '%S00001%' and bo.state=2 and tds.qstate!=3 and tds.state=2 and tds.city="+cityId+" group by bo.id  ORDER BY saleCount DESC,bo.id desc";
		return Db.paginate(pageNum, pageSize, sql, sqlwhere);
	}
	/***
	 * 根据商品ID查询商品规格	 
	 * @return
	 */
	public List<Record> getGoodsSpec(int goodsid) {
		return Db.find("select * from t_bus_goods_spec where goodId=?", goodsid);
	}
	/***
	 * 根据商品ID查询商品规格	 
	 * @return
	 */
	public Record getGoodsSpecType(int goodsid) {
		return Db.findFirst("select * from t_bus_goods_spec where goodId=?  LIMIT 1", goodsid);
	}
	/***
	 * 根据规格ID查询	 
	 * @return
	 */
	public Record getGoodsSpecId(int id) {
		return Db.findFirst("select * from t_bus_goods_spec where id=?", id);
	}
	/***
	 * 根据商品ID查询  和支付类型查询	 
	 * @return
	 */
	public Record getGoodsPaySpec(int id,int payType) {		
		if(payType==1){
			//积分支付
			return Db.findFirst("select * from t_bus_goods_spec where goodId=? and integral!=0 ", id);
		}else if(payType==2){
			//货币支付
			return Db.findFirst("select * from t_bus_goods_spec where goodId=? and currency!=0 ", id);
		}else if(payType==3){
			//积分+货币支付
			return Db.findFirst("select * from t_bus_goods_spec where goodId=? and integCur_i!=0 ", id);
		}else{
			return null;
		}	
	}
	/***
	 * 根据商品ID,规格id查询  和支付类型查询	 
	 * @return
	 */
	public Record getGoodsPaySpecId(int id,int payType,int specId) {		
		if(payType==1){
			//积分支付
			return Db.findFirst("select * from t_bus_goods_spec where goodId=? and integral!=0 and id=?", id,specId);
		}else if(payType==2){
			//货币支付
			return Db.findFirst("select * from t_bus_goods_spec where goodId=? and currency!=0 and id=?", id,specId);
		}else if(payType==3){
			//积分+货币支付
			return Db.findFirst("select * from t_bus_goods_spec where goodId=? and integCur_i!=0 and id=?", id,specId);
		}else{
			return null;
		}	
	}
	public List<Record> getHodingList() {
		return Db.find("select * from t_bus_home_window order by createDate desc");
	}
	/**
	 * 首页只查询两条数据
	 * @return
	 */
	public List<Record> getHodingHomeList() {
		return Db.find("select * from t_bus_home_window  order by createDate desc limit 0,2;");
	}
	
	
}
